﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Wicresoft.SMS.Data
{
    public class sqlReportWebDataAccess : IReportWebDataAccess, IHistoryWebDataAccess
    {
        private string _connectionString;

        public sqlReportWebDataAccess(string ConnectionString)
        {
            _connectionString = ConnectionString;
        }

        # region ReportArea
        public List<HistoryReport> QueryHistoryReport(DateTime StartTime, DateTime EndTime, queryFrequence Frequence)
        {
            switch (Frequence)
            {
                case queryFrequence.day:
                    return GetHistoryByDay(StartTime, EndTime);
                case queryFrequence.week:
                    return GetHistoryByWeek(StartTime, EndTime);
                case queryFrequence.mouth:
                    return GetHistoryByMouth(StartTime, EndTime);
                case queryFrequence.year:
                    return GetHistoryByYear(StartTime, EndTime);
                default:
                    return null;
            }
        }

        public List<GroupReport> QueryGroupReport(DateTime StartTime, DateTime EndTime, int MaxShow)
        {
            List<GroupReport> infos = new List<GroupReport>();
            using (var dbModel = new Entities(_connectionString))
            {
                var s = from a in dbModel.Message
                        join b in dbModel.User on a.UserID equals b.UserID
                        join c in dbModel.Department on b.DepartmentID equals c.DepartmentID
                        where a.CreateTime > StartTime
                        where a.CreateTime <= EndTime
                        where a.State / 48 >= 1
                        group c by new { c.DepartmentID, c.DepartmentName } into m
                        orderby m.Count() descending
                        select new
                        {
                            DepartmentID = m.Key.DepartmentID,
                            DepartmentName = m.Key.DepartmentName,
                            count = m.Count()
                        };
                int messageCount = dbModel.Message.Count(u => u.CreateTime > StartTime && u.CreateTime < EndTime && u.State / 48 >= 1);
                int remain = messageCount;
                var lists = s.ToList();
                if (lists != null && lists.Count() != 0)
                {
                    if (lists.Count() >= MaxShow)
                        lists = lists.Take(MaxShow).ToList();
                    foreach (var _list in lists)
                    {
                        GroupReport info = new GroupReport();
                        info.GroupID = _list.DepartmentID;
                        info.GroupName = _list.DepartmentName;
                        info.Count = _list.count;
                        info.Percent = (_list.count * 100 / messageCount).ToString() + "%";
                        remain -= _list.count;
                        infos.Add(info);
                    }
                    GroupReport _info = new GroupReport();
                    _info.GroupName = "others";
                    _info.Count = remain;
                    infos.Add(_info);
                }



            }
            return infos;
        }

        public List<IndividualReprot> QueryIndividualReprot(int GroupId, DateTime StartTime, DateTime EndTime, int MaxShow)
        {
            List<IndividualReprot> infos = new List<IndividualReprot>();
            using (var dbModel = new Entities(_connectionString))
            {
                if (GroupId != 0)
                {
                    var s = from a in dbModel.Message
                            join b in dbModel.User on a.UserID equals b.UserID
                            join c in dbModel.Department on b.DepartmentID equals c.DepartmentID
                            where a.CreateTime > StartTime
                            where a.CreateTime <= EndTime
                            where a.State / 48 >= 1
                            where b.DepartmentID == GroupId
                            group b by new { b.UserID, b.NameEN, b.DepartmentID, DepartmentName = c.DepartmentName } into m
                            orderby m.Count() descending
                            select new
                            {
                                UserID = m.Key.UserID,
                                UserName = m.Key.DepartmentName,
                                GroupID = m.Key.DepartmentID,
                                GroupName = m.Key.DepartmentName,
                                count = m.Count()
                            };
                    int messageCount = (from a in dbModel.Message
                                        join b in dbModel.User on a.UserID equals b.UserID
                                        where a.CreateTime > StartTime
                                        where a.CreateTime <= EndTime
                                        where b.DepartmentID == GroupId
                                        where a.State / 48 >= 1
                                        select new { }).Count();
                    int remain = messageCount;
                    var lists = s.ToList();
                    if (lists != null && lists.Count() != 0)
                    {
                        if (lists.Count() >= MaxShow)
                            lists = lists.Take(MaxShow).ToList();
                        foreach (var _list in lists)
                        {
                            IndividualReprot info = new IndividualReprot();
                            //info.GroupId = _list.GroupID;
                            info.GroupName = _list.GroupName.ToString();
                            info.UserId = _list.UserID;
                            info.GroupName = _list.UserName;
                            info.Count = _list.count;
                            remain -= _list.count;
                            infos.Add(info);
                        }
                        IndividualReprot _info = new IndividualReprot();
                        _info.GroupName = "others";
                        _info.Count = remain;
                        infos.Add(_info);
                    }
                }
                else
                {
                    var s = from a in dbModel.Message
                            join b in dbModel.User on a.UserID equals b.UserID
                            join c in dbModel.Department on b.DepartmentID equals c.DepartmentID
                            where a.CreateTime > StartTime
                            where a.CreateTime <= EndTime
                            where a.State / 48 >= 1
                            group b by new { b.UserID, b.NameEN, b.DepartmentID, b.DeptName } into m
                            orderby m.Count() descending
                            select new
                            {
                                GroupID = m.Key.DepartmentID,
                                GroupName = m.Key.DeptName,
                                UserID = m.Key.UserID,
                                UserName = m.Key.NameEN,
                                count = m.Count()
                            };
                    int messageCount = dbModel.Message.Count(u => u.CreateTime > StartTime && u.CreateTime < EndTime && u.State / 48 >= 1);
                    int remain = messageCount;
                    var lists = s.ToList();
                    if (lists != null && lists.Count() != 0)
                    {
                        if (lists.Count() >= MaxShow)
                            lists = lists.Take(MaxShow).ToList();
                        foreach (var _list in lists)
                        {
                            IndividualReprot info = new IndividualReprot();
                            //info.GroupId = _list.GroupID;
                            info.GroupName = _list.GroupName.ToString();
                            info.UserId = _list.UserID;
                            info.UserName = _list.UserName;
                            info.Count = _list.count;
                            remain -= _list.count;
                            infos.Add(info);
                        }
                        IndividualReprot _info = new IndividualReprot();
                        _info.GroupName = "others";
                        _info.Count = remain;
                        infos.Add(_info);
                    }
                }



            }
            return infos;
        }

        public string GetDepartnameById(int DepartmentId)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var Departname = dbModel.Department.FirstOrDefault(u => u.DepartmentID == DepartmentId);
                if (Departname != null)
                    return Departname.DepartmentName;
            }
            return null;

        }

        public int GetTotalMessage(DateTime StartTime)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var count = dbModel.Message.Count(u => u.CreateTime > StartTime && (u.State % 100) / 48 >= 1);
                return count;
            }
        }

        public int GetTotalFaliedMessage(DateTime StartTime)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var count = dbModel.Message.Count(u => u.CreateTime > StartTime && ((u.State % 100) < 48 && (u.State % 100) >= 32));
                return count;
            }
        }

        private List<HistoryReport> GetHistoryByDay(DateTime StartTime, DateTime EndTime)
        {
            List<HistoryReport> Infos = new List<HistoryReport>();
            using (var dbModel = new Entities(_connectionString))
            {
                while (StartTime < EndTime)
                {
                    DateTime _endTime = StartTime.AddDays(1);
                    var count = dbModel.Message.Count(u => u.CreateTime <= _endTime && u.CreateTime > StartTime && u.State / 48 >= 1);
                    HistoryReport _info = new HistoryReport();
                    _info.Time = StartTime.ToString("m");
                    _info.Count = count;
                    Infos.Add(_info);
                    StartTime = _endTime;
                }
            }
            return Infos;
        }
        private List<HistoryReport> GetHistoryByWeek(DateTime StartTime, DateTime EndTime)
        {
            List<HistoryReport> Infos = new List<HistoryReport>();
            using (var dbModel = new Entities(_connectionString))
            {
                while (StartTime < EndTime)
                {
                    DateTime _endTime = StartTime.AddDays(7);
                    var count = dbModel.Message.Count(u => u.CreateTime <= _endTime && u.CreateTime > StartTime && u.State / 48 >= 1);
                    HistoryReport _info = new HistoryReport();
                    _info.Time = StartTime.ToString("m") + "~" + (_endTime < EndTime ? _endTime.AddMinutes(-1).ToString("m") : EndTime.ToString("m"));
                    _info.Count = count;
                    Infos.Add(_info);
                    StartTime = _endTime;
                }
            }
            return Infos;
        }
        private List<HistoryReport> GetHistoryByMouth(DateTime StartTime, DateTime EndTime)
        {
            List<HistoryReport> Infos = new List<HistoryReport>();
            using (var dbModel = new Entities(_connectionString))
            {
                while (StartTime < EndTime)
                {
                    DateTime _endTime = StartTime.AddMonths(1);
                    var count = dbModel.Message.Count(u => u.CreateTime <= _endTime && u.CreateTime > StartTime && u.State / 48 >= 1);
                    HistoryReport _info = new HistoryReport();
                    _info.Time = StartTime.ToString("MMM");
                    _info.Count = count;
                    Infos.Add(_info);
                    StartTime = _endTime;
                }
            }
            return Infos;
        }
        private List<HistoryReport> GetHistoryByYear(DateTime StartTime, DateTime EndTime)
        {
            List<HistoryReport> Infos = new List<HistoryReport>();
            using (var dbModel = new Entities(_connectionString))
            {
                while (StartTime < EndTime)
                {
                    DateTime _endTime = StartTime.AddYears(1);
                    var count = dbModel.Message.Count(u => u.CreateTime <= _endTime && u.CreateTime > StartTime && u.State / 48 >= 1);
                    HistoryReport _info = new HistoryReport();
                    _info.Time = StartTime.ToString("yyyy");
                    _info.Count = count;
                    Infos.Add(_info);
                    StartTime = _endTime;
                }
            }
            return Infos;
        }
        #endregion

        #region HistoryArea
        public List<HistoryInfoModel> GetHistoryByAll(int PageIndex, int MaxShow, ViewHistoryType Type, string Search, int BatchId)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                List<Message> s = new List<Message>();
                if (String.IsNullOrEmpty(Search))
                {
                    Search = String.Empty;
                }
                if (BatchId == 0)
                {
                    switch (Type)
                    {
                        case ViewHistoryType.pending:
                            s = (from a in dbModel.Message
                                 join b in dbModel.User on a.UserID equals b.UserID
                                 where a.State < 32
                                 where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                                 orderby a.CreateTime descending
                                 select a).Skip((PageIndex - 1) * MaxShow).Take(MaxShow).ToList<Message>();
                            break;
                        case ViewHistoryType.failed:
                            s = (from a in dbModel.Message
                                 join b in dbModel.User on a.UserID equals b.UserID
                                 where a.State < 48 && a.State >= 32
                                 where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                                 orderby a.CreateTime descending
                                 select a).Skip((PageIndex - 1) * MaxShow).Take(MaxShow).ToList<Message>();
                            break;
                        case ViewHistoryType.delivered:
                            s = (from a in dbModel.Message
                                 join b in dbModel.User on a.UserID equals b.UserID
                                 where a.State >= 48 && a.State < 100
                                 where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                                 orderby a.CreateTime descending
                                 select a).Skip((PageIndex - 1) * MaxShow).Take(MaxShow).ToList<Message>();
                            break;
                    }
                }
                else
                {
                    switch (Type)
                    {
                        case ViewHistoryType.pending:
                            s = (from a in dbModel.Message
                                 where a.State < 32
                                 where a.BatchID == BatchId
                                 orderby a.CreateTime descending
                                 select a).Skip((PageIndex - 1) * MaxShow).Take(MaxShow).ToList<Message>();
                            break;
                        case ViewHistoryType.failed:
                            s = (from a in dbModel.Message
                                 where a.State < 48 && a.State >= 32
                                 where a.BatchID == BatchId
                                 orderby a.CreateTime descending
                                 select a).Skip((PageIndex - 1) * MaxShow).Take(MaxShow).ToList<Message>();
                            break;
                        case ViewHistoryType.delivered:
                            s = (from a in dbModel.Message
                                 where a.State >= 48 && a.State < 100
                                 where a.BatchID == BatchId
                                 orderby a.CreateTime descending
                                 select a).Skip((PageIndex - 1) * MaxShow).Take(MaxShow).ToList<Message>();
                            break;
                    }
                }

                List<HistoryInfoModel> list = new List<HistoryInfoModel>();
                foreach (var message in s)
                {
                    HistoryInfoModel _list = new HistoryInfoModel();
                    if (message.Content.Length <= 10)
                        _list.ContentShort = message.Content;
                    else
                        _list.ContentShort = message.Content.Substring(0, 10) + "...";
                    _list.Content = message.Content;
                    _list.From = GetUserNameById(message.UserID);
                    _list.Id = message.ID.ToString();
                    _list.SentTime = message.CreateTime.ToString("yyyy-MM-dd HH:mm:ss");
                    _list.To = message.To;
                    list.Add(_list);
                }
                return list;
            }
            throw new NotImplementedException();
        }

        public List<HistoryInfoModel> GetHistoryByGroup(int PageIndex, int MaxShow, int GroupId, ViewHistoryType Type, string Search)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                List<Message> s = new List<Message>();
                if (String.IsNullOrEmpty(Search))
                {
                    Search = String.Empty;
                }

                switch (Type)
                {
                    case ViewHistoryType.pending:
                        s = (from a in dbModel.Message
                             join b in dbModel.User on a.UserID equals b.UserID
                             where a.State < 32
                             where b.DepartmentID == GroupId
                             where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                             orderby a.CreateTime descending
                             select a).Skip((PageIndex - 1) * MaxShow).Take(MaxShow).ToList<Message>();
                        break;
                    case ViewHistoryType.failed:
                        s = (from a in dbModel.Message
                             join b in dbModel.User on a.UserID equals b.UserID
                             where a.State < 48 && a.State >= 32
                             where b.DepartmentID == GroupId
                             where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                             orderby a.CreateTime descending
                             select a).Skip((PageIndex - 1) * MaxShow).Take(MaxShow).ToList<Message>();
                        break;
                    case ViewHistoryType.delivered:
                        s = (from a in dbModel.Message
                             join b in dbModel.User on a.UserID equals b.UserID
                             where a.State >= 48 && a.State < 100
                             where b.DepartmentID == GroupId
                             where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                             orderby a.CreateTime descending
                             select a).Skip((PageIndex - 1) * MaxShow).Take(MaxShow).ToList<Message>();
                        break;
                }

                List<HistoryInfoModel> list = new List<HistoryInfoModel>();
                foreach (var message in s)
                {
                    HistoryInfoModel _list = new HistoryInfoModel();
                    if (message.Content.Length <= 10)
                        _list.ContentShort = message.Content;
                    else
                        _list.ContentShort = message.Content.Substring(0, 10) + "...";
                    _list.Content = message.Content;
                    _list.From = GetUserNameById(message.UserID);
                    _list.Id = message.ID.ToString();
                    _list.SentTime = message.CreateTime.ToString();
                    _list.To = message.To;
                    list.Add(_list);
                }
                return list;
            }
            throw new NotImplementedException();
        }

        public List<HistoryInfoModel> GetHistoryByUser(int PageIndex, int MaxShow, int UserId, ViewHistoryType Type, string Search, int batchid)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                List<Message> s = new List<Message>();
                if (String.IsNullOrEmpty(Search))
                {
                    Search = String.Empty;
                }

                switch (Type)
                {
                    case ViewHistoryType.pending:
                        s = (from a in dbModel.Message
                             join b in dbModel.User on a.UserID equals b.UserID
                             where a.State < 32
                             where a.UserID == UserId
                             where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                             orderby a.CreateTime descending
                             select a).Skip((PageIndex - 1) * MaxShow).Take(MaxShow).ToList<Message>();
                        break;
                    case ViewHistoryType.failed:
                        if(batchid==0)
                        s = (from a in dbModel.Message
                             join b in dbModel.User on a.UserID equals b.UserID
                             where a.State < 48 && a.State >= 32
                             where a.UserID == UserId
                             where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                             orderby a.CreateTime descending
                             select a).Skip((PageIndex - 1) * MaxShow).Take(MaxShow).ToList<Message>();
                        else
                            s = (from a in dbModel.Message
                                 join b in dbModel.User on a.UserID equals b.UserID
                                 where a.State < 48 && a.State >= 32
                                 where a.UserID == UserId
                                 where a.BatchID == batchid
                                 where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                                 orderby a.CreateTime descending
                                 select a).Skip((PageIndex - 1) * MaxShow).Take(MaxShow).ToList<Message>();
                        break;
                    case ViewHistoryType.delivered:
                        s = (from a in dbModel.Message
                             join b in dbModel.User on a.UserID equals b.UserID
                             where a.State >= 48 && a.State < 100
                             where a.UserID == UserId
                             where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                             orderby a.CreateTime descending
                             select a).Skip((PageIndex - 1) * MaxShow).Take(MaxShow).ToList<Message>();
                        break;
                }

                List<HistoryInfoModel> list = new List<HistoryInfoModel>();
                foreach (var message in s)
                {
                    HistoryInfoModel _list = new HistoryInfoModel();
                    if (message.Content.Length <= 10)
                        _list.ContentShort  = message.Content;
                    else
                        _list.ContentShort = message.Content.Substring(0, 10) + "...";
                    _list.Content = message.Content;
                    _list.From = GetUserNameById(message.UserID);
                    _list.Id = message.ID.ToString();
                    _list.SentTime = message.CreateTime.ToString();
                    _list.To = message.To;
                    list.Add(_list);
                }
                return list;
            }
            throw new NotImplementedException();
        }

        public int GetPageCount(ViewHistoryType Type, ViewHistoryRole Role, int Id, int MaxShow, string Search, int BatchId)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                int pagecount = 0;
                if (String.IsNullOrEmpty(Search))
                {
                    Search = String.Empty;
                }
                if (BatchId == 0)
                {
                    switch (Type)
                    {
                        case ViewHistoryType.pending:
                            switch (Role)
                            {
                                case ViewHistoryRole.All:
                                    pagecount = ((from a in dbModel.Message
                                                  join b in dbModel.User on a.UserID equals b.UserID
                                                  where a.State < 32
                                                  where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                                                  select a).Count() + (MaxShow - 1)) / MaxShow;
                                    break;
                                case ViewHistoryRole.Group:
                                    pagecount = ((from a in dbModel.Message
                                                  join b in dbModel.User on a.UserID equals b.UserID
                                                  where a.State < 32 && b.UserID == Id
                                                  where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                                                  select a).Count() + (MaxShow - 1)) / MaxShow;
                                    break;
                                case ViewHistoryRole.User:
                                    pagecount = ((from a in dbModel.Message
                                                  join b in dbModel.User on a.UserID equals b.UserID
                                                  where a.State < 32 && a.UserID == Id
                                                  where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                                                  select a).Count() + (MaxShow - 1)) / MaxShow;
                                    break;
                            }
                            break;
                        case ViewHistoryType.failed:
                            switch (Role)
                            {
                                case ViewHistoryRole.All:
                                    pagecount = ((from a in dbModel.Message
                                                  join b in dbModel.User on a.UserID equals b.UserID
                                                  where a.State >= 32 && a.State < 48
                                                  where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                                                  select a).Count() + (MaxShow - 1)) / MaxShow;
                                    break;
                                case ViewHistoryRole.Group:
                                    pagecount = ((from a in dbModel.Message
                                                  join b in dbModel.User on a.UserID equals b.UserID
                                                  where a.State >= 32 && a.State < 48 && b.DepartmentID ==Id
                                                  where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                                                  select a).Count() + (MaxShow - 1)) / MaxShow;
                                    break;
                                case ViewHistoryRole.User:
                                    pagecount = ((from a in dbModel.Message
                                                  join b in dbModel.User on a.UserID equals b.UserID
                                                  where a.State >= 32 && a.State < 48 && a.UserID == Id
                                                  where b.NameEN.Contains(Search) || a.To.Contains(Search) || a.Content.Contains(Search)
                                                  select a).Count() + (MaxShow - 1)) / MaxShow;
                                    break;
                            }
                            break;
                        case ViewHistoryType.delivered:
                            switch (Role)
                            {
                                case ViewHistoryRole.All:
                                    pagecount = ((from a in dbModel.Message
                                                  where a.State >= 48 && a.State < 100
                                                 // where a.BatchID == BatchId
                                                  select a).Count() + (MaxShow - 1)) / MaxShow;
                                    break;
                                case ViewHistoryRole.Group:
                                    pagecount = ((from a in dbModel.Message
                                                  join b in dbModel.User on a.UserID equals b.UserID
                                                  where a.State >= 48 && a.State < 100 && b.DepartmentID == Id
                                                 // where a.BatchID == BatchId
                                                  select a).Count() + (MaxShow - 1)) / MaxShow;
                                    break;
                                case ViewHistoryRole.User:
                                    pagecount = ((from a in dbModel.Message
                                                  where a.State >= 48 && a.State < 100 && a.UserID == Id
                                                  //where a.BatchID == BatchId
                                                  select a).Count() + (MaxShow - 1)) / MaxShow;
                                    break;
                            }
                            break;
                    }
                }
                else
                {
                    switch (Type)
                    {
                        case ViewHistoryType.pending:
                            pagecount = ((from a in dbModel.Message
                                          where a.State < 32
                                          where a.BatchID == BatchId
                                          select a).Count() + (MaxShow - 1)) / MaxShow;
                            break;
                        case ViewHistoryType.failed:
                            pagecount = ((from a in dbModel.Message
                                          where a.State >= 32 && a.State < 48
                                          where a.BatchID == BatchId
                                          select a).Count() + (MaxShow - 1)) / MaxShow;
                            break;
                        case ViewHistoryType.delivered:
                            pagecount = ((from a in dbModel.Message
                                          where a.State >= 48 && a.State < 100
                                          where a.BatchID == BatchId
                                          select a).Count() + (MaxShow - 1)) / MaxShow;
                            break;
                    }
                }
                return pagecount;
            }
            throw new NotImplementedException();
        }

        public bool ResentMessage(int BatchId)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var info = dbModel.Message.Where(e => e.State >= 32 && e.State < 48 && e.BatchID == BatchId);
                foreach (var message in info)
                {
                    message.Content += DateTime.Now.ToString();
                    message.State = 0;
                    message.ModifyTime = DateTime.Now;
                }
                try
                {
                    dbModel.SaveChanges();
                }
                catch
                {
                    return false;
                }
                return true;
            }
            throw new NotImplementedException();
        }

        public bool ResentMessageById(int Id)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var info = dbModel.Message.FirstOrDefault(e => e.ID == Id);
                info.Content += " [" + DateTime.Now.ToString() + "] ";
                info.State = 0;
                info.CreateTime = DateTime.Now;
                info.ModifyTime = DateTime.Now;
                try
                {
                    dbModel.SaveChanges();
                }
                catch
                {
                    return false;
                }
                return true;
            }
            throw new NotImplementedException();
        }

        public bool DeleteMessageById(int Id)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var info = dbModel.Message.SingleOrDefault(e => e.ID == Id);
                //dbModel.Message.DeleteObject(info);
                info.State += 100;
                try
                {
                    dbModel.SaveChanges();
                }
                catch
                {
                    return false;
                }
                return true;
            }
            throw new NotImplementedException();
        }

        private string GetUserNameById(int UserId)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                string username = dbModel.User.FirstOrDefault(u => u.UserID == UserId).NameEN;
                return username;
            }
        }

        private int GetGroupIdByUserId(int UserId)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                int groupId = dbModel.User.FirstOrDefault(u => u.UserID == UserId).DepartmentID ?? 0;
                return groupId;
            }
        }
        #endregion


        public int UserInfoPage(int max, string key)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var s = from n in dbModel.User
                        where n.NameCN.Contains(key) || n.NameEN.Contains(key)
                        where n.IsActive == 4
                        select n;
                int count = (s.Count() + (max - 1)) / max;
                return count;
            }
        }


        public List<UsersInfo> QueryUsesrReports(string key, int page, int max, DateTime starttime, DateTime endtime, int ordertype)
        {
            using (var dbModel = new Entities(_connectionString))
            {

                var users = (from u in dbModel.User
                            where u.NameCN.Contains(key) || u.NameEN.Contains(key) || u.DeptName.Contains(key)
                            where u.IsActive == 4
                            orderby u.UserID
                             select u);//.Skip((page - 1) * max).Take(max);
                List<UsersInfo> list = new List<UsersInfo>();
                foreach (var item in users)
                {
                    UsersInfo ui = new UsersInfo();
                    ui.CIL = item.CIL;
                    ui.NameEN = item.NameEN;
                    ui.Email = item.Email;
                    ui.UserId = item.UserID;
                    ui.NameCN = item.NameCN;
                    ui.DeptName = item.DeptName;
                    list.Add(ui);
                }
                foreach (var user in list)
                {
                    var count = (from me in dbModel.Message
                                 where me.UserID==user.UserId
                                 where me.CreateTime <= endtime && me.CreateTime > starttime
                                 where (me.State % 100) >= 48
                                 select me).Count();
                    user.UserCount = count;
                }

                if (ordertype == 1)
                    list = list.OrderByDescending(a => a.UserCount).ToList();
                if (ordertype == 2)
                    list = list.OrderBy(a => a.UserCount).ToList();

                list = list.Skip((page - 1) * max).Take(max).ToList(); ;
                
                return list;
            }
            throw new NotImplementedException();
        }


        public List<UsersInfo> Getdata(DateTime starttime, DateTime endtime)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var userinfo = from u in dbModel.User
                               where u.IsActive == 4
                               select u;
                List<UsersInfo> list = new List<UsersInfo>();
                foreach (var item in userinfo)
                {
                    UsersInfo ui = new UsersInfo();
                    ui.CIL = item.CIL;
                    ui.NameEN = item.NameEN;
                    ui.Email = item.Email;
                    ui.NameCN = item.NameCN;
                    ui.UserId = item.UserID;
                    ui.DeptName = item.DeptName;
                    list.Add(ui);
                }
                foreach (var user in list)
                {
                    var count = (from me in dbModel.Message
                                 where me.UserID == user.UserId
                                 where me.CreateTime <= endtime && me.CreateTime > starttime
                                 where (me.State % 100) >= 48
                                 select me).Count();
                    user.UserCount = count;
                }
                return list;
            }
           
            throw new NotImplementedException();
        }
    }


}
